import pandas as pd
import matplotlib.pyplot as plt

plt.rcParams['font.family'] = 'Microsoft YaHei'

# 1. 加载三张表数据
file_path_balance = '../雪龙集团资产负债表.xlsx'
file_path_profit = '../雪龙集团利润表.xlsx'

# 读取资产负债表
df_balance = pd.read_excel(file_path_balance, header=None)
df_balance.columns = ["项目"] + [2020, 2021, 2022, 2023]

# 读取利润表
df_profit = pd.read_excel(file_path_profit, header=None)
df_profit.columns = ["项目"] + [2020, 2021, 2022, 2023]

# 转换数值列（去除逗号并转换为数字）
for col in [2020, 2021, 2022, 2023]:
    df_balance[col] = pd.to_numeric(df_balance[col].replace(',', '', regex=True), errors='coerce')
    df_profit[col] = pd.to_numeric(df_profit[col].replace(',', '', regex=True), errors='coerce')

# 2. 提取所需数据
# 从利润表提取营业收入
revenue = df_profit[df_profit["项目"] == "营业总收入(元)"].iloc[0, 1:]

# 从资产负债表提取总资产和应收账款
total_assets = df_balance[df_balance["项目"] == "资产总计(元)"].iloc[0, 1:]
receivables = df_balance[df_balance["项目"] == "应收帐款(元)"].iloc[0, 1:]

# 3. 计算平均总资产和平均应收账款
average_total_assets = (total_assets.shift(1) + total_assets) / 2  # 平均总资产
average_receivables = (receivables.shift(1) + receivables) / 2  # 平均应收账款

# 4. 计算总资产周转率和应收账款周转率
total_asset_turnover = revenue / average_total_assets  # 总资产周转率
receivables_turnover = revenue / average_receivables  # 应收账款周转率

# 5. 绘制折线图
plt.figure(figsize=(10, 6))

# 绘制总资产周转率
plt.plot([2020, 2021, 2022, 2023], total_asset_turnover, marker='o', label="总资产周转率", color='blue')

# 绘制应收账款周转率
plt.plot([2020, 2021, 2022, 2023], receivables_turnover, marker='o', label="应收账款周转率", color='green')

# 添加标题和标签
plt.title("雪龙集团2020-2023年运营能力分析", fontsize=16)
plt.xlabel("年份", fontsize=12)
plt.ylabel("周转率", fontsize=12)

# 添加图例和网格
plt.legend(loc="best", fontsize=10)
plt.grid(alpha=0.5)
plt.tight_layout()

plt.savefig('../分析图象/运营能力分析图表.png')
# 显示图表
plt.show()
